package org.scboy.coobi.sql;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.scboy.coobi.caches.CoobiContext;
import org.scboy.coobi.caches.DbTableCache;
import org.scboy.coobi.caches.SqlCache;
import org.scboy.coobi.log.DataAlterLog;
import org.scboy.coobi.properties.CoobiProperties;
import org.scboy.coobi.template.DBTemplate;
import org.scboy.utils.entitys.coobi.ChildQuery;
import org.scboy.utils.entitys.coobi.DbColumn;
import org.scboy.utils.entitys.coobi.DbIndex;
import org.scboy.utils.entitys.coobi.DbTable;
import org.scboy.utils.entitys.coobi.Limit;
import org.scboy.utils.entitys.coobi.Model;
import org.scboy.utils.entitys.coobi.Page;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;

@Repository
public class Coobi {
	
	Log log=LogFactory.get();
	
	@Autowired
	JdbcTemplate template;
	
	@Autowired
	CoobiProperties properties;

	public DataAlterLog getDataAlterLog() {
		return SpringUtil.getBean(DataAlterLog.class);
	}
	
	public int executeUpdateSqlId(String sqlId,Model params) {
		DBTemplate dbTemplate=CoobiContext.coobiTemplate;
		
		//处理sql中IN的参数
		String sql=dbTemplate.getSql(SqlCache.getConfig(sqlId).getSqlEntity().getSql(), params);
		
		//处理参数
		LinkedHashMap<Integer, String> keyMap = new LinkedHashMap<Integer, String>();
		sql=dbTemplate.initSql(sql, keyMap);
		
		//设置参数
		List<Object> objectParams=new LinkedList<Object>();
		for (Integer index : keyMap.keySet()) {
			Object value=params.get(keyMap.get(index));
			objectParams.add(value);
		}
		
		//SQL转换
		sql=CoobiContext.dbStyle.sqlFormat(sql);
		if(properties.isShowSql()) {
			log.info("执行SQL:"+sql);
		}
		return template.update(sql,objectParams.toArray());
	}
	
	public int executeInsert(String table,Model params) {
		
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="INSERT INTO "+table;
		if(params.size()==0) {
			throw new RuntimeException("无参数，无法新增。");
		}else {
			
			List<DbColumn> columns=dbTable.getColumns();
			
			String sqlColumns="";
			String sqlParams="";
			
			//参数
			LinkedList<Object> objectParams=new LinkedList<Object>();
			for (DbColumn dbColumn : columns) {
				objectParams.add(params.get(dbColumn.getEntityName()));
				if(sqlColumns.length()==0) {
					sqlColumns=dbColumn.getName();
					sqlParams="?";
				}else {
					sqlColumns+=","+dbColumn.getName();
					sqlParams+=",?";
				}
			}
			
			sql+=" ("+sqlColumns+") VALUES ("+sqlParams+")";

			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			
			
			int count= template.update(sql,objectParams.toArray());
			
			DataAlterLog alertLog = getDataAlterLog();
			
			//如果开启了数据日志，记录日志。
			if(alertLog!=null && count>0 && alertLog.checkAlter(table)) {
				alertLog.insertAlter(dbTable,params,sql,objectParams.toArray());
			}
			
			return count;
		}
	}
	
	public int executeUpdate(String table,Model params) {
		
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="UPDATE "+table +" SET ";
		if(params.size()>0) {
			
			List<DbColumn> columns=dbTable.getColumns();
			String sqlValues="";
			
			List<DbColumn> primarys=new ArrayList<DbColumn>();
			
			LinkedList<Object> objectParams=new LinkedList<Object>();
			//构建SQL语句
			for (DbColumn column : columns) {
				if(params.containsKey(column.getEntityName())) {
					objectParams.add(params.get(column.getEntityName()));
					if(sqlValues.length()==0) {
						sqlValues=column.getName()+" = ? ";
					}else {
						sqlValues+=","+column.getName()+" = ? ";
					}
				}
				if(column.isPrimary()) {
					primarys.add(column);
				}
			}

			sql+=sqlValues;
			
			if(primarys.size()==0) {
				throw new RuntimeException(table+"表无主键，无法执行更新操作。");
			}
			
			String sqlParams="";
			
			//构建改变数据的查询条件
			Model queryParams=Model.create();
			//构建查询条件
			for (DbColumn column : primarys) {
				queryParams.add(column.getEntityName(), params.get(column.getEntityName()));
				objectParams.add(params.get(column.getEntityName()));
				if(sqlParams.length()==0) {
					sqlParams=column.getName()+" = ? ";
				}else {
					sqlParams+=" AND "+column.getName()+" = ? ";
				}
			}
			
			if(sqlParams.length()==0) {
				throw new RuntimeException("无法更新"+table+"，参数没有主键值。");
			}
			
			if(StrUtil.isNotEmpty(sqlParams)) {
				sql+=" WHERE "+sqlParams;
			}
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}

			DataAlterLog alertLog = getDataAlterLog();
			
			//如果修改查询开启了变更日志，那么先查询历史记录
			List<Model> oldAlterDataList=null;
			if(alertLog!=null && alertLog.checkAlter(table)) {
				oldAlterDataList=executeQuery(table, queryParams);
			}
			
			int count= template.update(sql,objectParams.toArray());
			//如果开启了数据日志，记录日志。
			if(alertLog!=null && count>0 && alertLog.checkAlter(table)) {
				alertLog.updateAlter(dbTable,params,oldAlterDataList,sql,objectParams.toArray());
			}
			return count;
		}else {
			throw new RuntimeException("无更新信息，无效调用。");
		}
	}
	

	
	public int executeUpdate(String table,Model data,Model params) {
		
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="UPDATE "+table +" SET ";
		List<DbColumn> columns=dbTable.getColumns();
		String sqlValues="";
		
		LinkedList<Object> objectParams=new LinkedList<Object>();
		//构建SQL语句
		for (DbColumn column : columns) {
			if(data.containsKey(column.getEntityName())) {
				objectParams.add(data.get(column.getEntityName()));
				if(sqlValues.length()==0) {
					sqlValues=column.getName()+" = ? ";
				}else {
					sqlValues+=","+column.getName()+" = ? ";
				}
			}
		}

		sql+=sqlValues;
		
		String sqlParams="";
		try {
			sqlParams = getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		if(StrUtil.isNotEmpty(sqlParams)){
			sql+=" WHERE "+sqlParams;
		}
		
		//SQL转换
		sql=CoobiContext.dbStyle.sqlFormat(sql);
		if(properties.isShowSql()) {
			log.info("执行SQL:"+sql);
		}

		DataAlterLog alertLog = getDataAlterLog();
		
		//如果修改查询开启了变更日志，那么先查询历史记录
		List<Model> oldAlterDataList=null;
		if(alertLog!=null && alertLog.checkAlter(table)) {
			oldAlterDataList=executeQuery(table, params);
		}
		
		int count = template.update(sql,objectParams.toArray());
		
		//如果开启了数据日志，记录日志。
		if(alertLog!=null && count>0 && alertLog.checkAlter(table)) {
			alertLog.updateAlter(dbTable,data,oldAlterDataList,sql,objectParams.toArray());
		}
		
		return count;
	}
	
	public int executeDelete(String table,Model params) {

		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="DELETE FROM "+table;
		
		//构建条件语句
		String sqlWhere="";
		List<DbColumn> columns=dbTable.getColumns();
		LinkedList<Object> objectParams=new LinkedList<Object>();
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		//如果删除查询开启了变更日志，那么先查询历史记录
		List<Model> oldAlterDataList=null;

		DataAlterLog alertLog = getDataAlterLog();
		
		if(alertLog!=null && alertLog.checkAlter(table)) {
			oldAlterDataList=executeQuery(table, params);
		}
		
		if(StrUtil.isNotEmpty(sqlWhere)) {
			
			sql+=" WHERE " + sqlWhere;

			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			int count = template.update(sql,objectParams.toArray());
			//如果开启了数据日志，记录日志。
			if(alertLog!=null && count>0 && alertLog.checkAlter(table)) {
				alertLog.deleteAlter(dbTable,oldAlterDataList,sql,objectParams.toArray());
			}
			return count;
		}else {
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			int count = template.update(sql);
			//如果开启了数据日志，记录日志。
			if(alertLog!=null && count>0 && alertLog.checkAlter(table)) {
				alertLog.deleteAlter(dbTable,oldAlterDataList,sql,objectParams.toArray());
			}
			return count;
		}
	}
	
	public List<Model> executeQuerySqlId(String sqlId,Model params) {
		
		DBTemplate dbTemplate=CoobiContext.coobiTemplate;
		
		//处理sql中IN的参数
		String sql=dbTemplate.getSql(SqlCache.getConfig(sqlId).getSqlEntity().getSql(), params);
		
		//处理参数
		LinkedHashMap<Integer, String> keyMap = new LinkedHashMap<Integer, String>();
		sql=dbTemplate.initSql(sql, keyMap);
		
		//设置参数
		List<Object> objectParams=new LinkedList<Object>();
		for (Integer index : keyMap.keySet()) {
			Object value=params.get(keyMap.get(index));
			objectParams.add(value);
		}
		
		//SQL转换
		sql=CoobiContext.dbStyle.sqlFormat(sql);
		if(properties.isShowSql()) {
			log.info("执行SQL:"+sql);
		}
		List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
		List<Model> result=new ArrayList<Model>();
		
		for (Map<String, Object> data : datas) {
			Model rs=Model.create();
			for (String key : data.keySet()) {
				rs.add(key, data.get(key));
			}
			result.add(rs);
		}
		return result;
	}
	
	public List<Model> executeQuery(String table,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		
		String sql="SELECT ";
		
		List<DbColumn> columns=dbTable.getColumns();
		String sqlColumns="";
		LinkedList<Object> objectParams=new LinkedList<Object>();

		//构建分页SQL
		String orderBySql=getOrderSql(columns, params);
		
		//构建SQL语句
		for (DbColumn column : columns) {
			//检查是否允许返回
			if(!params.checkRc(column.getEntityName())) {
				continue;
			}
			if(sqlColumns.length()==0) {
				sqlColumns = column.getName()+" AS "+column.getEntityName();
			}else {
				sqlColumns += ","+column.getName()+" AS "+column.getEntityName();
			}
		}
		
		sql+=sqlColumns+" FROM "+table;
		
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		if(StrUtil.isNotEmpty(sqlWhere)) {
			
			sql+=" WHERE "+sqlWhere;
			
			//加入排序SQL
			sql+=orderBySql;
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			List<Model> result=new ArrayList<Model>();
			
			for (Map<String, Object> data : datas) {
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				result.add(rs);
			}
			return result;
		}else {

			//加入排序SQL
			sql+=orderBySql;
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas =  template.queryForList(sql);
			List<Model> result=new ArrayList<Model>();
			
			for (Map<String, Object> data : datas) {
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				result.add(rs);
			}
			return result;
		}
	}
	
	/**
	 * 查询条目数
	 * @param sqlId
	 * @param params
	 * @return
	 */
	public long executeQueryCountSqlId(String sqlId,Model params) {
		
		DBTemplate dbTemplate=CoobiContext.coobiTemplate;
		
		//处理sql中IN的参数
		String sql=dbTemplate.getSql(SqlCache.getConfig(sqlId).getSqlEntity().getSql(), params);
		
		//处理参数
		LinkedHashMap<Integer, String> keyMap = new LinkedHashMap<Integer, String>();
		sql=dbTemplate.initSql(sql, keyMap);
		
		//设置参数
		List<Object> objectParams=new LinkedList<Object>();
		for (Integer index : keyMap.keySet()) {
			Object value=params.get(keyMap.get(index));
			objectParams.add(value);
		}

		sql="SELECT COUNT(*) AS _COUNT_NUM FROM ("+sql+") _TMP_TABLE";
		//SQL转换
		sql=CoobiContext.dbStyle.sqlFormat(sql);
		if(properties.isShowSql()) {
			log.info("执行SQL:"+sql);
		}
		List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
		Long result=0L;
		
		if(datas.size() > 0) {
			Map<String, Object> data=datas.get(0);
			if(data.get("_COUNT_NUM")!=null) {
				result=Convert.toLong(data.get("_COUNT_NUM"));
			}
		}
		return result;
	}
	
	/**
	 * 查询条目数
	 * @param table
	 * @param params
	 * @return
	 */
	public long executeQueryCount(String table,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="SELECT COUNT(*) AS _COUNT_NUM FROM "+table;
		
		List<DbColumn> columns=dbTable.getColumns();
		LinkedList<Object> objectParams=new LinkedList<Object>();
		
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		if(StrUtil.isNotEmpty(sqlWhere)) {
			
			sql+=" WHERE "+sqlWhere;
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			Long result=0L;
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				if(data.get("_COUNT_NUM")!=null) {
					result=Convert.toLong(data.get("_COUNT_NUM"));
				}
			}
			return result;
		}else {
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas =  template.queryForList(sql);
			Long result=0L;
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				if(data.get("_COUNT_NUM")!=null) {
					result=Convert.toLong(data.get("_COUNT_NUM"));
				}
			}
			return result;
		}
	}
	
	/**
	 * 求最大值
	 * @param table
	 * @param entityNames
	 * @param params
	 * @return
	 */
	public Model executeQueryMax(String table,String [] entityNames,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="";
		
		List<DbColumn> columns=dbTable.getColumns();
		
		for (DbColumn dbColumn : columns) {
			for (String entityName : entityNames) {
				if(dbColumn.getEntityName().equals(entityName)) {
					if(StrUtil.isEmpty(sql)) {
						sql="MAX("+dbColumn.getName()+") AS "+entityName;
					}else {
						sql=sql+",MAX("+dbColumn.getName()+") AS "+entityName;
					}
				}
			}
		}
		sql="SELECT "+sql+" FROM "+table;
		
		LinkedList<Object> objectParams=new LinkedList<Object>();
		
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		if(StrUtil.isNotEmpty(sqlWhere)) {
			
			
			sql+=" WHERE "+sqlWhere;
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}else {
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas =  template.queryForList(sql);
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}
	}
	
	/**
	 * 求最小值
	 * @param table
	 * @param entityNames
	 * @param params
	 * @return
	 */
	public Model executeQueryMin(String table,String [] entityNames,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="";
		
		List<DbColumn> columns=dbTable.getColumns();
		
		for (DbColumn dbColumn : columns) {
			for (String entityName : entityNames) {
				if(dbColumn.getEntityName().equals(entityName)) {
					if(StrUtil.isEmpty(sql)) {
						sql="MIN("+dbColumn.getName()+") AS "+entityName;
					}else {
						sql=sql+",MIN("+dbColumn.getName()+") AS "+entityName;
					}
				}
			}
		}
		sql="SELECT "+sql+" FROM "+table;
		
		LinkedList<Object> objectParams=new LinkedList<Object>();
		
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		if(StrUtil.isNotEmpty(sqlWhere)) {
			
			
			sql+=" WHERE "+sqlWhere;
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}else {
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas =  template.queryForList(sql);
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}
	}
	
	/**
	 * 求和
	 * @param table
	 * @param entityNames
	 * @param params
	 * @return
	 */
	public Model executeQuerySum(String table,String [] entityNames,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="";
		
		List<DbColumn> columns=dbTable.getColumns();
		
		for (DbColumn dbColumn : columns) {
			for (String entityName : entityNames) {
				if(dbColumn.getEntityName().equals(entityName)) {
					if(StrUtil.isEmpty(sql)) {
						sql="SUM("+dbColumn.getName()+") AS "+entityName;
					}else {
						sql=sql+",SUM("+dbColumn.getName()+") AS "+entityName;
					}
				}
			}
		}
		sql="SELECT "+sql+" FROM "+table;
		
		LinkedList<Object> objectParams=new LinkedList<Object>();
		
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		if(StrUtil.isNotEmpty(sqlWhere)) {
			
			sql+=" WHERE "+sqlWhere;
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}else {
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas =  template.queryForList(sql);
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}
	}
	
	/**
	 * 求平均值
	 * @param table
	 * @param entityNames
	 * @param params
	 * @return
	 */
	public Model executeQueryAvg(String table,String [] entityNames,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="";
		
		List<DbColumn> columns=dbTable.getColumns();
		
		for (DbColumn dbColumn : columns) {
			for (String entityName : entityNames) {
				if(dbColumn.getEntityName().equals(entityName)) {
					if(StrUtil.isEmpty(sql)) {
						sql="AVG("+dbColumn.getName()+") AS "+entityName;
					}else {
						sql=sql+",AVG("+dbColumn.getName()+") AS "+entityName;
					}
				}
			}
		}
		sql="SELECT "+sql+" FROM "+table;
		
		LinkedList<Object> objectParams=new LinkedList<Object>();
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		if(StrUtil.isNotEmpty(sqlWhere)) {
			
			sql+=" WHERE "+sqlWhere;
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}else {
			
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("执行SQL:"+sql);
			}
			List<Map<String, Object>> datas =  template.queryForList(sql);
			if(datas.size() > 0) {
				Map<String, Object> data=datas.get(0);
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				return rs;
			}else {
				return null;
			}
		}
	}
	
	/**
	 * 根据配置SQL分页查询
	 * @param sqlId
	 * @param params
	 * @param limit
	 * @return
	 */
	public Page executePageSqlId(String sqlId,Model params) {
		DBTemplate dbTemplate=CoobiContext.coobiTemplate;
		
		//处理sql中IN的参数
		String sql=dbTemplate.getSql(SqlCache.getConfig(sqlId).getSqlEntity().getSql(), params);
		
		//处理参数
		LinkedHashMap<Integer, String> keyMap = new LinkedHashMap<Integer, String>();
		sql=dbTemplate.initSql(sql, keyMap);
		
		//设置参数
		List<Object> objectParams=new LinkedList<Object>();
		for (Integer index : keyMap.keySet()) {
			Object value=params.get(keyMap.get(index));
			objectParams.add(value);
		}
		//查询总条数
		String sqlCount= "SELECT COUNT(*) AS QUERY_COUNT  FROM ("+sql+") TEMP_QUERY_COUNT";
		Map<String, Object> countMap = template.queryForMap(sqlCount, objectParams.toArray());

		//SQL转换
		sqlCount=CoobiContext.dbStyle.sqlFormat(sqlCount);
		if(properties.isShowSql()) {
			log.info("分页条目数SQL:"+sqlCount);
		}
		Limit limit=params.getLimit();
		//构建分页SQL
		sql = CoobiContext.dbStyle.getPageSql(sql, limit.getStart(), limit.getLimit());

		//SQL转换
		sql=CoobiContext.dbStyle.sqlFormat(sql);
		if(properties.isShowSql()) {
			log.info("分页结果集SQL:"+sql);
		}
		List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
		List<Model> result=new ArrayList<Model>();
		
		for (Map<String, Object> data : datas) {
			Model rs=Model.create();
			for (String key : data.keySet()) {
				rs.add(key, data.get(key));
			}
			result.add(rs);
		}
		
		//查询条数
		return new Page(result,Convert.toLong(countMap.get("QUERY_COUNT")));
	}
	

	/**
	 * 根据表配置，进行分页查询
	 * @param table
	 * @param params
	 * @param limit
	 * @return
	 */
	public Page executePage(String table,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="SELECT ";
		
		List<DbColumn> columns=dbTable.getColumns();
		String sqlColumns="";
		LinkedList<Object> objectParams=new LinkedList<Object>();

		//构建分页SQL
		String orderBySql=getOrderSql(columns, params);
		
		//构建SQL语句
		for (DbColumn column : columns) {
			//检查是否允许返回
			if(!params.checkRc(column.getEntityName())) {
				continue;
			}
			if(sqlColumns.length()==0) {
				sqlColumns = column.getName()+" AS "+column.getEntityName();
			}else {
				sqlColumns += ","+column.getName()+" AS "+column.getEntityName();
			}
		}

		sql+=sqlColumns+" FROM "+table;
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		Limit limit=params.getLimit();
		
		//如果查询条件存在
		if(StrUtil.isNotEmpty(sqlWhere)) {
			sql+=" WHERE "+sqlWhere;
			//查询总条数
			String sqlCount= "SELECT COUNT(*) AS QUERY_COUNT  FROM ("+sql+") TEMP_QUERY_COUNT";
			Map<String, Object> countMap = template.queryForMap(sqlCount, objectParams.toArray());
			

			//SQL转换
			sqlCount=CoobiContext.dbStyle.sqlFormat(sqlCount);
			if(properties.isShowSql()) {
				log.info("分页条目数SQL:"+sqlCount);
			}
			//加入排序SQL
			sql+=orderBySql;
			
			//构建分页SQL
			sql = CoobiContext.dbStyle.getPageSql(sql, limit.getStart(), limit.getLimit());
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("分页结果集SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			
			List<Model> result=new ArrayList<Model>();
			
			for (Map<String, Object> data : datas) {
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				result.add(rs);
			}
			
			//查询条数
			return new Page(result,Convert.toLong(countMap.get("QUERY_COUNT")));
		}else {
			//查询总条数
			String sqlCount= "SELECT COUNT(*) AS QUERY_COUNT  FROM ("+sql+") TEMP_QUERY_COUNT";

			//SQL转换
			sqlCount=CoobiContext.dbStyle.sqlFormat(sqlCount);
			Map<String, Object> countMap = template.queryForMap(sqlCount);
			if(properties.isShowSql()) {
				log.info("分页条目数SQL:"+sqlCount);
			}

			//加入排序SQL
			sql+=orderBySql;
			
			//构建分页SQL
			sql = CoobiContext.dbStyle.getPageSql(sql, limit.getStart(), limit.getLimit());
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("分页结果集SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql);
			
			List<Model> result=new ArrayList<Model>();
			
			for (Map<String, Object> data : datas) {
				Model rs=Model.create();
				for (String key : data.keySet()) {
					rs.add(key, data.get(key));
				}
				result.add(rs);
			}
			
			//查询条数
			return new Page(result,Convert.toLong(countMap.get("QUERY_COUNT")));
		}
	}
	
	public Model executeGetSqlId(String sqlId,Model params) {
		DBTemplate dbTemplate=CoobiContext.coobiTemplate;
		
		//处理sql中IN的参数
		String sql=dbTemplate.getSql(SqlCache.getConfig(sqlId).getSqlEntity().getSql(), params);
		
		//处理参数
		LinkedHashMap<Integer, String> keyMap = new LinkedHashMap<Integer, String>();
		sql=dbTemplate.initSql(sql, keyMap);
		
		//设置参数
		List<Object> objectParams=new LinkedList<Object>();
		for (Integer index : keyMap.keySet()) {
			Object value=params.get(keyMap.get(index));
			objectParams.add(value);
		}
		//构建分页SQL
		sql = CoobiContext.dbStyle.getPageSql(sql, 0, 1);
		//SQL转换
		sql=CoobiContext.dbStyle.sqlFormat(sql);
		if(properties.isShowSql()) {
			log.info("执行SQL:"+sql);
		}
		List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
		if(datas.size()==0) {
			return null;
		}else {
			Model result=Model.create();
			for (String key : datas.get(0).keySet()) {
				result.add(key, datas.get(0).get(key));
			}
			return result;
		}
	}
	
	/**
	 * 获取一条数据
	 * @param table
	 * @param params
	 * @return
	 */
	public Model executeGet(String table,Model params) {
		DbTable dbTable=DbTableCache.getDbTable(table);
		
		String sql="SELECT ";
		
		List<DbColumn> columns=dbTable.getColumns();
		String sqlColumns="";
		LinkedList<Object> objectParams=new LinkedList<Object>();
		
		//构建SQL语句
		for (DbColumn column : columns) {
			//检查是否允许返回
			if(!params.checkRc(column.getEntityName())) {
				continue;
			}
			if(sqlColumns.length()==0) {
				sqlColumns = column.getName()+" AS "+column.getEntityName();
			}else {
				sqlColumns += ","+column.getName()+" AS "+column.getEntityName();
			}
		}

		sql+=sqlColumns+" FROM "+table;
		
		String sqlWhere="";
		try {
			sqlWhere=getSqlWhere(columns, params, objectParams);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		//构建分页SQL
		String orderBySql=getOrderSql(columns, params);
		
		if(StrUtil.isNotEmpty(sqlWhere)) {
			sql+=" WHERE "+sqlWhere;
			//加入排序SQL
			sql+=orderBySql;
			//构建分页SQL
			sql = CoobiContext.dbStyle.getPageSql(sql, 0, 1);
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("分页结果集SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql,objectParams.toArray());
			if(datas.size()==0) {
				return null;
			}else {
				Model result=Model.create();
				for (String key : datas.get(0).keySet()) {
					result.add(key, datas.get(0).get(key));
				}
				return result;
			}
		}else {
			//加入排序SQL
			sql+=orderBySql;
			//构建分页SQL
			sql = CoobiContext.dbStyle.getPageSql(sql, 0, 1);
			//SQL转换
			sql=CoobiContext.dbStyle.sqlFormat(sql);
			if(properties.isShowSql()) {
				log.info("分页结果集SQL:"+sql);
			}
			List<Map<String, Object>> datas = template.queryForList(sql);
			if(datas.size()==0) {
				return null;
			}else {
				Model result=Model.create();
				for (String key : datas.get(0).keySet()) {
					result.add(key, datas.get(0).get(key));
				}
				return result;
			}
		}
	}
	
	/**
	 * 通过类，构建查询条件
	 * @param columns
	 * @param params
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	private String getSqlWhere(List<DbColumn> columns,Map<String, Object> params,LinkedList<Object> values) throws SQLException{
		if(params==null)return "";
		Connection connection=template.getDataSource().getConnection();
		String url=connection.getMetaData().getURL();
		connection.close();
		//查询条件
		StringBuilder sqlWhere=new StringBuilder("");
		//排序sql
		StringBuilder sqlOrder=new StringBuilder("");
		//先处理子查询
		if(params.containsKey("coobi_db_or_and_query")){

			List<ChildQuery> listQuery=(List<ChildQuery>)params.get("coobi_db_or_and_query");
			for (ChildQuery childQuery : listQuery) {
				String childSqlWhere=getSqlWhere(columns, childQuery.getQueryModel(), values);
				String join="";
				if(sqlWhere.length()!=0){
					join=" "+childQuery.getJoin()+" ";
				}
				if(childSqlWhere.length()>0){
					sqlWhere.append(join);
					sqlWhere.append("( ");
					sqlWhere.append(childSqlWhere);
					sqlWhere.append(") ");
				}
			}
		}
		//处理查询字段
		for (DbColumn  column : columns) {
			String colName=column.getName();
			String entityName=column.getEntityName();
			//倒序排列
			if(params.containsKey(entityName+"-desc")){
				if(sqlOrder.length()==0) {
					sqlOrder.append(colName+" DESC");
				}else {
					sqlOrder.append(","+colName+" DESC");
				}
			}else if(params.containsKey(entityName+"-asc")){
				if(sqlOrder.length()==0) {
					sqlOrder.append(colName+" ASC");
				}else {
					sqlOrder.append(","+colName+" ASC");
				}
			}
			
			//条件相关 and
			//等于
			if(params.containsKey(entityName)){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				if(params.get(entityName)==null){
					sqlWhere.append(join+colName+" IS NULL");
				}else if(params.get(entityName) instanceof String 
						&&url.indexOf("jdbc:oracle")>-1
						&& StrUtil.isEmpty((String)params.get(entityName))){
					//解决oracle的''和null是一个值得问题。
					sqlWhere.append(join+colName+" IS NULL");
				}else{
					sqlWhere.append(join+colName+" = ?");
					values.add(params.get(entityName));
				}
			}
			//小于
			if(params.containsKey(entityName+"-lt")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				sqlWhere.append(join+colName+" < ?");
				values.add(params.get(entityName+"-lt"));
			}
			//大于
			if(params.containsKey(entityName+"-gt")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				sqlWhere.append(join+colName+" > ?");
				values.add(params.get(entityName+"-gt"));
			}
			//大于等于
			if(params.containsKey(entityName+"-ge")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				sqlWhere.append(join+colName+" >= ?");
				values.add(params.get(entityName+"-ge"));
			}
			//小于等于
			if(params.containsKey(entityName+"-le")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				sqlWhere.append(join+colName+" <= ?");
				values.add(params.get(entityName+"-le"));
			}
			//不等于
			if(params.containsKey(entityName+"-ne")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				if(params.get(entityName+"-ne")==null){
					sqlWhere.append(join+colName+" IS NOT NULL");
				}else if(params.get(entityName+"-ne") instanceof String 
						&&url.indexOf("jdbc:oracle")>-1
						&& StrUtil.isEmpty((String)params.get(entityName+"-ne"))){
					//解决oracle的''和null是一个值得问题。
					sqlWhere.append(join+colName+" IS NOT NULL");
				}else{
					sqlWhere.append(join+colName+" <> ?");
					values.add( params.get(entityName+"-ne"));
				}
			}
			//模糊查询
			if(params.containsKey(entityName+"-lk")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				sqlWhere.append(join+colName+" LIKE ?");
				values.add(params.get(entityName+"-lk"));
			}
			//包含查询
			if(params.containsKey(entityName+"-in")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				sqlWhere.append(join+colName+" in (");
				Object inValue=params.get(entityName+"-in");
				StringBuilder inSql=new StringBuilder("");
				if(inValue instanceof ArrayList||inValue instanceof List){
					for (Object inv : (List<?>)inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else if(inValue.getClass().isArray()){
					
					for (Object inv : (Object[])inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else {
					inSql.append("?,");
					values.add(inValue);
				}
				sqlWhere.append(inSql.substring(0,inSql.length()-1));
				sqlWhere.append(")");
			}
			//不包含查询
			if(params.containsKey(entityName+"-nin")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" AND ";
				}
				sqlWhere.append(join+column.getName()+" NOT IN (");
				Object inValue=params.get(entityName+"-nin");
				StringBuilder inSql=new StringBuilder("");
				if(inValue instanceof List){
					for (Object inv : (List<?>)inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else if(inValue.getClass().isArray()){
					for (Object inv : (Object[])inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else {
					inSql.append("?,");
					values.add(inValue);
				}
				sqlWhere.append(inSql.substring(0,inSql.length()-1));
				sqlWhere.append(")");
			}
			
			//条件相关 or
			//等于
			if(params.containsKey(entityName+"-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				if(params.get(entityName+"-or")==null){
					sqlWhere.append(join+colName+" IS NULL");
				}else if(params.get(entityName+"-or") instanceof String 
						&&url.indexOf("jdbc:oracle")>-1
						&& StrUtil.isEmpty((String)params.get(entityName+"-or"))){
					//解决oracle的''和null是一个值得问题。
					sqlWhere.append(join+colName+" IS NULL");
				}else{
					sqlWhere.append(join+colName+" = ?");
					values.add(params.get(entityName+"-or"));
				}
			}
			//小于
			if(params.containsKey(entityName+"-lt-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				sqlWhere.append(join+colName+" < ?");
				values.add(params.get(entityName+"-lt-or"));
			}
			//大于
			if(params.containsKey(entityName+"-gt-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				sqlWhere.append(join+colName+" > ?");
				values.add(params.get(entityName+"-gt-or"));
			}
			//大于等于
			if(params.containsKey(entityName+"-ge-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				sqlWhere.append(join+colName+" >= ?");
				values.add(params.get(entityName+"-ge-or"));
			}
			//小于等于
			if(params.containsKey(entityName+"-le-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				sqlWhere.append(join+colName+" <= ?");
				values.add(params.get(entityName+"-le-or"));
			}
			//不等于
			if(params.containsKey(entityName+"-ne-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				if(params.get(entityName+"-ne-or")==null){
					sqlWhere.append(join+colName+" IS NOT NULL");
				}else if(params.get(entityName+"-ne-or") instanceof String 
						&&url.indexOf("jdbc:oracle")>-1
						&& StrUtil.isEmpty((String)params.get(entityName+"-ne-or"))){
					//解决oracle的''和null是一个值得问题。
					sqlWhere.append(join+colName+" IS NOT NULL");
				}else{
					sqlWhere.append(join+colName+" <> ?");
					values.add(params.get(entityName+"-ne-or"));
				}
			}
			//模糊查询
			if(params.containsKey(entityName+"-lk-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				sqlWhere.append(join+colName+" LIKE ?");
				values.add(params.get(entityName+"-lk-or"));
			}
			//包含查询
			if(params.containsKey(entityName+"-in-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				sqlWhere.append(join+colName+" IN (");
				Object inValue=params.get(entityName+"-in-or");
				StringBuilder inSql=new StringBuilder("");
				if(inValue instanceof ArrayList||inValue instanceof List){
					for (Object inv : (List<?>)inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else if(inValue.getClass().isArray()){
					
					for (Object inv : (Object[])inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else {
					inSql.append("?,");
					values.add(inValue);
				}
				sqlWhere.append(inSql.substring(0,inSql.length()-1));
				sqlWhere.append(")");
			}
			//不包含查询
			if(params.containsKey(entityName+"-nin-or")){
				String join="";
				if(sqlWhere.length()!=0){
					join=" OR ";
				}
				sqlWhere.append(join+column.getName()+" NOT IN (");
				Object inValue=params.get(entityName+"-nin-or");
				StringBuilder inSql=new StringBuilder("");
				if(inValue instanceof List){
					for (Object inv : (List<?>)inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else if(inValue.getClass().isArray()){
					for (Object inv : (Object[])inValue) {
						inSql.append("?,");
						values.add(inv);
					}
				}else {
					inSql.append("?,");
					values.add(inValue);
				}
				sqlWhere.append(inSql.substring(0,inSql.length()-1));
				sqlWhere.append(")");
			}
		}
		if(sqlWhere.length()>0){
			return sqlWhere.toString();
		}else{
			return "";
		}
	}
	/**
	 * 获取分页SQL
	 * @param conlumns
	 * @return
	 */
	private String getOrderSql(List<DbColumn> conlumns,Model params){
		StringBuilder orderSql=new StringBuilder("");
		//获取排序列
		Map<Integer, String> orderCloumns=new LinkedHashMap<Integer, String>();
		for (DbColumn dbColumn : conlumns) {
			String entityName=dbColumn.getEntityName();
			//判断是否是排序列
			if(params.containsKey(entityName+"-ASC")) {
				orderCloumns.put(params.getInt(entityName+"-ASC"),dbColumn.getName()+" ASC");
			}else if(params.containsKey(entityName+"-DESC")) {
				orderCloumns.put(params.getInt(entityName+"-DESC"),dbColumn.getName()+" DESC");
			}
		}
		
		//排序
		Map<Integer, String>  sortCloumns=orderCloumns.entrySet().stream().sorted((o1,o2)->{
			return o1.getKey().compareTo(o2.getKey());
		}).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue,(e1,e2)->e1,LinkedHashMap::new));
		
		//构建排序SQL
		for (Integer key : sortCloumns.keySet()) {
			if(orderSql.length()==0){
				orderSql.append(" ORDER BY "+sortCloumns.get(key));
			}else {
				orderSql.append(","+sortCloumns.get(key));
			}
		}
		return orderSql.toString();
	}

	/**
	 * 检查表/列是否存在
	 * @param tabName
	 * @param colName 为null检查表
	 * @return
	 */
	public boolean exists(String tabName,String colName){
		String sqlString=CoobiContext.dbStyle.getExistsColumnSql(tabName, colName);
		return template.queryForList(sqlString).size()>0? true:false;
	}
	
	/**
	 * 创建表
	 * @param entity
	 * @return
	 */
	public boolean createTable(DbTable entity){
		String sql=CoobiContext.dbStyle.getCreateTableSql(entity);
		template.update(sql);
		//create语句没有返回值，重新检查一次表。
		if(this.exists(entity.getName(),null)){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 创建列
	 * @param taName
	 * @param column
	 * @return
	 */
	public boolean createColumn(String taName, DbColumn column){
		String sql=CoobiContext.dbStyle.getCreateColumnSql(taName, column);
		try {
			template.update(sql);
			try {
				if(StrUtil.isNotEmpty(column.getComment())){
					String commentSql=CoobiContext.dbStyle.getAddColumnCommentSql(taName, column);
					template.update(commentSql);
				}
			} catch (Exception e) {
				log.warn("为{}[{}]添加备注错误。{}",taName,column.getName(),e.getMessage());
			}
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	/**
	 * 删除列
	 * @param taName
	 * @param column
	 * @return
	 */
	public boolean deleteColumn(String taName, String columnName){
		String sql=CoobiContext.dbStyle.getDeleteColumnSql(taName, columnName);
		int result=template.update(sql);
		if(result>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 创建表索引
	 * @param taName
	 * @param column
	 * @return
	 */
	public boolean createIndex(String taName, DbIndex dbIndex){
		String sql=CoobiContext.dbStyle.getCreateIndexSql(taName, dbIndex);
		template.update(sql);
		if(this.existsIndex(taName,dbIndex.getIndexName())){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 删除索引
	 * @param taName
	 * @param column
	 * @return
	 */
	public boolean deleteIndex(String taName, String indexName){
		String sql=CoobiContext.dbStyle.getDeleteIndexSql(taName, indexName);
		int result=template.update(sql);
		if(result>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 检查列是否存在
	 * @param taName
	 * @param indexName
	 * @return
	 */
	public boolean existsIndex(String taName, String indexName){
		String sql=CoobiContext.dbStyle.getExistsIndexSql(taName, indexName);
		return template.queryForList(sql).size()>0? true:false;
	}
}
